package com.infore.dataReceiving.repository;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.infore.dataReceiving.entity.RealTimeData;
import com.infore.dataReceiving.entity.ZeroCheckData;


@Repository
public interface IRealTimeDataRepository extends JpaRepository<RealTimeData, String>, JpaSpecificationExecutor<RealTimeData>  {
	
	public List<RealTimeData> findByDataTimeBetweenOrderByDataTimeDesc(Timestamp beginTime,Timestamp endTime);

	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select * from ("+
			"select rownum no,c.mn,c.dataTime,c.w01010,c.w01001,c.w01009,c.w01003,c.w01014,c.w01019,c.w21003,c.w21011,c.w21001,c.w01018,c.w01016 " + 
			"from " + 
			"(select mn,data_time dataTime, " + 
			"avg(case when factor_code='w01010' then REAL_VALUE else null end) w01010," + 
			"avg(case when factor_code='w01001' then REAL_VALUE else null end) w01001," + 
			"avg(case when factor_code='w01009' then REAL_VALUE else null end) w01009," + 
			"avg(case when factor_code='w01003' then REAL_VALUE else null end) w01003," + 
			"avg(case when factor_code='w01014' then REAL_VALUE else null end) w01014," + 
			"avg(case when factor_code='w01019' then REAL_VALUE else null end) w01019," + 
			"avg(case when factor_code='w21003' then REAL_VALUE else null end) w21003," + 
			"avg(case when factor_code='w21011' then REAL_VALUE else null end) w21011," + 
			"avg(case when factor_code='w21001' then REAL_VALUE else null end) w21001," + 
			"avg(case when factor_code='w01018' then REAL_VALUE else null end) w01018," + 
			"avg(case when factor_code='w01016' then REAL_VALUE else null end) w01016 " + 
			"from REAL_TIME_DATA where mn in (:stationList) and data_time between to_date(:beginTime,'yyyy-MM-dd HH24:mi:ss') and  "
			+ "to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "group by mn,data_time order by data_time desc) c " + 
			"where rownum<=:endSize"+
			" ) table_alias WHERE table_alias.no >=:startSize")
	public List<Map<String, Object>> findRealTimeData(List<String> stationList,String beginTime,String endTime,int endSize,int startSize);

	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select count(*) sl from("+
			"select count(*) sl " + 
			"from REAL_TIME_DATA where mn in (:stationList) and data_time between to_date(:beginTime,'yyyy-MM-dd HH24:mi:ss') and  "
			+ "to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "group by mn,data_time order by data_time desc) aa")
	public Integer findRealTimeDataCount(List<String> stationList,String beginTime,String endTime);
	
	public List<RealTimeData> findByUpdateFlagIsNullAndMnInOrderByDataTimeAsc(List<String> stationCodes);
	
	@Modifying
	@Transactional 
	@Query(nativeQuery = true,value = "UPDATE REAL_TIME_DATA SET UPDATE_FLAG = :flag "
			+ "WHERE REAL_TIME_ID IN (:ids) ")
	int updateRealTimeData(@Param("flag")String flag, @Param("ids") List<String> ids);

	/**
	 * 综合应用得到实时数据---站点因子分组
	 */
	@Query(value = "select t.mn as MN ,t.factor_code as CODE,to_char( avg(t.real_value),'fm999999990.999999999') as VALUE from REAL_TIME_DATA t " + 
			"where t.mn in (:mnList) and t.factor_code in (:projectCodes) and (t.data_time between (to_date(:startTime, 'yyyy-mm-dd hh24:mi:ss')) and  (to_date(:endTime, 'yyyy-mm-dd hh24:mi:ss'))) " + 
			"group by t.mn,t.factor_code " + 
			"order by t.mn asc",nativeQuery = true)
	List<Map<String, Object>> queryGroupByMnCode(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);

	/**综合应用得到实时数据---不分组
	 **/
	@Query(value = "select to_char(t.data_time, 'yyyy-mm-dd hh24:mi:ss') as TIME,t.mn as MN,t.factor_code as CODE,t.real_value as VALUE " + 
			"from Real_Time_Data t where (t.mn in (:mnList)) and (t.factor_code in (:projectCodes)) and (t.data_time between (to_date(:startTime, 'yyyy-mm-dd hh24:mi:ss')) and (to_date(:endTime, 'yyyy-mm-dd hh24:mi:ss'))) " + 
			"order by t.data_time asc ",nativeQuery = true)
	List<Map<String, Object>> queryByMnCode(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);
}
